﻿USE [SST]
GO
/****** Object:  User [sst]    Script Date: 6/7/2013 6:55:20 PM ******/
CREATE USER [sst] FOR LOGIN [sst] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [sst]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [sst]
GO
/****** Object:  StoredProcedure [dbo].[AgregarAsignacion]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[AgregarAsignacion]
	-- Add the parameters for the stored procedure here
	@Informatico INT,
	@Habilidad INT,
	@Circuito INT,
	@RealizadaPor INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    INSERT INTO Asignacion(Informatico,Habilidad,Circuito, RealizadaPor, Fecha) 
    VALUES(@Informatico,@Habilidad, @circuito, @RealizadaPor, GETDATE())
	SELECT 1;
END

GO
/****** Object:  StoredProcedure [dbo].[AgregarExcepcion]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[AgregarExcepcion]
	-- Add the parameters for the stored procedure here
	@Informatico INT,
	@Comienzo DATETIME,
	@Fin DATETIME
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    INSERT INTO Excepcion(Informatico, Comienzo, HoraTiempo) 
	VALUES(@Informatico, @Comienzo, @Fin)
	SELECT 1;

	RETURN;
END

GO
/****** Object:  StoredProcedure [dbo].[AgregarHorario]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[AgregarHorario]
	@NombreHorario VARCHAR(50),
	@HoraInicio INT,
	@HoraFin INT,
	@Dias VARCHAR(7)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    INSERT INTO Horario(NombreHorario, HoraInicio, HoraFin, Dias) 
	VALUES(@NombreHorario, @HoraInicio, @HoraFin, @Dias)
	SELECT 1;
END

GO
/****** Object:  StoredProcedure [dbo].[AgregarInformatico]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[AgregarInformatico]
	-- Add the parameters for the stored procedure here
	@nombre VARCHAR(20),
	@nombreRol INT,
	@circuitoID INT,
	@horario INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SET xact_abort ON;

    BEGIN TRAN T1;
	
		DECLARE @result INT

		INSERT INTO Informatico( NombreCompleto,Horario) VALUES(@nombre, @horario)
		DECLARE @IDEN INT
		SET @IDEN = @@IDENTITY
		INSERT INTO RolInformatico(InformaticoID, RolID) VALUES(@IDEN, @nombreRol)
		INSERT INTO InformaticoCircuito(CircuitoID, InformaticoID) VALUES( @circuitoID , @IDEN ) 
		SELECT 1;
	COMMIT TRAN T1;

	RETURN;
END

GO
/****** Object:  StoredProcedure [dbo].[BorrarExcepcion]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[BorrarExcepcion]
	-- Add the parameters for the stored procedure here
	@ExcepcionID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    UPDATE Excepcion SET FechaBorrado = GETDATE() WHERE ExcepcionID = @ExcepcionID
	SELECT 1;
END

GO
/****** Object:  StoredProcedure [dbo].[EditarHorario]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[EditarHorario]
	-- Add the parameters for the stored procedure here
	@HorarioID INT, 
	@NombreHorario VARCHAR(50),
	@HoraInicio INT,
	@HoraFin INT,
	@Dias VARCHAR(7)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    UPDATE Horario 
	SET 
	NombreHorario = @NombreHorario ,
	HoraInicio = @HoraInicio,
	HoraFin = @HoraFin,
	Dias = @Dias
	WHERE 
	HorarioID = @HorarioID
	SELECT 1;
END

GO
/****** Object:  StoredProcedure [dbo].[EliminarHorario]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[EliminarHorario]
	-- Add the parameters for the stored procedure here
	@ID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    UPDATE Horario SET FechaBorrado = GETDATE() WHERE HorarioID = @ID
	SELECT 1
END

GO
/****** Object:  StoredProcedure [dbo].[EliminarInformatico]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[EliminarInformatico]
	-- Add the parameters for the stored procedure here
	@ID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

    DECLARE @InformaticoID INT
	SET @InformaticoID = @ID
	UPDATE Informatico SET FechaBorrado = GETDATE() WHERE InformaticoID = @InformaticoID
	UPDATE RolInformatico SET FechaBorrado = GETDATE() WHERE InformaticoID = @InformaticoID
	SELECT 1;
	RETURN;
END

GO
/****** Object:  StoredProcedure [dbo].[EsCoordinador]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[EsCoordinador]
	-- Add the parameters for the stored procedure here
	@InformaticoID BIGINT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT COUNT(InformaticoID) 'EsCoordinador'
	FROM Informatico
	WHERE InformaticoID = @InformaticoID
	AND EsCoordinador = 1 
	AND FechaBorrado IS NULL
END

GO
/****** Object:  StoredProcedure [dbo].[GetAsignaciones]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetAsignaciones]	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT TOP 10 a.AsignacionID, c.NombreCircuito , i.NombreCompleto 'Nombre del Informatico', irp.NombreCompleto 'Operario', a.Fecha
	FROM asignacion as a 
	INNER JOIN Circuito as c ON a.Circuito=c.CircuitoID
	INNER JOIN Habilidad as h ON a.Habilidad=h.HabilidadID
	INNER JOIN Informatico as i ON i.InformaticoID = a.Informatico	
	INNER JOIN Informatico as irp ON irp.InformaticoID = a.RealizadaPor
	AND i.FechaBorrado IS NULL
	ORDER BY a.AsignacionID DESC
END

GO
/****** Object:  StoredProcedure [dbo].[GetCircuito]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetCircuito]
	-- Add the parameters for the stored procedure here
	@nombre VARCHAR(20),
	@codigo VARCHAR(20)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT nombres.CircuitoID, nombres.NombreCircuito, nombres.CodigoCircuito 
	FROM 
	(SELECT CircuitoID, NombreCircuito, CodigoCircuito 
	FROM Circuito 
	WHERE FechaBorrado IS NULL AND NombreCircuito = @nombre) as nombres
	INNER JOIN 
	(SELECT CircuitoID, NombreCircuito, CodigoCircuito 
	FROM Circuito 
	WHERE FechaBorrado IS NULL AND CodigoCircuito = @codigo) as codigos
	ON nombres.CircuitoID=codigos.CircuitoID
END

GO
/****** Object:  StoredProcedure [dbo].[GetHorario]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetHorario]
	@HorarioID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT HorarioID, NombreHorario, HoraInicio, HoraFin, Dias 
	FROM Horario
	WHERE FechaBorrado IS NULL AND HorarioID = @HorarioID
END

GO
/****** Object:  StoredProcedure [dbo].[GetInformaticoParaAsignacion]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetInformaticoParaAsignacion]
	-- Add the parameters for the stored procedure here
	@Circuito BIGINT,
	@Habilidad BIGINT
AS
BEGIN
	SET NOCOUNT ON;

/***
Este stored procedure se usa para encontrar cual informatico sigue en el periodo de asignaciones,
se filtra por la habilidad y el circuito luego se verifica que el usuario este en el horario
despues de esto se verifica que no tenga una excepcion(vacaciones, salida, entre otros).

El algoritmo es sencillo. :)
*/

--Declaran variable para el manejo de los horarios
DECLARE @HoraActual int --Contiene la hora en formato de 24 horas
DECLARE @Dia VARCHAR(1) -- el Dia en formato LKMJVSD

--se usa para poder manajar los dias. 
SET @HoraActual = DATEPART(hh, GETDATE()) 
SELECT @Dia = CASE DATEPART(weekday,GETDATE()) 
WHEN 1 THEN 'L'
WHEN 2 THEN 'K' /*Martes se maneja como K por estar internacional*/
WHEN 3 THEN 'M'
WHEN 4 THEN 'J'
WHEN 5 THEN 'V'
WHEN 6 THEN 'S'
WHEN 7 THEN 'D'
END

DECLARE @count INT /*Contiene la cantidad de informaticos disponibles para la asignacion*/
SET @count = (
SELECT COUNT( DISTINCT i1.InformaticoID) FROM Informatico as i1 
INNER JOIN InformaticoCircuito as ic ON i1.InformaticoID = ic.InformaticoID /*Circuito*/
INNER JOIN Circuito as c ON ic.CircuitoID = c.CircuitoID

INNER JOIN RolInformatico as ri ON i1.InformaticoID=ri.InformaticoID /* Rol*/
INNER JOIN Rol as r ON ri.RolID=r.RolID		

INNER JOIN InformaticoHabilidad as ih ON i1.InformaticoID=ih.InformaticoID /*Habilidad*/
INNER JOIN Horario as hor ON i1.Horario=hor.HorarioID

LEFT JOIN (SELECT Informatico, HoraTiempo FROM Excepcion /*Manejo de Expceciones*/
WHERE Comienzo < GETDATE() AND HoraTiempo < GETDATE() AND FechaBorrado IS NULL)
as e ON i1.InformaticoID=e.Informatico

WHERE ic.CircuitoID = @Circuito AND r.NombreRol = 'Personal de Soporte' AND ih.HabilidadID = @Habilidad /*Habilidades y Filtrar por personal de soporte*/
AND (hor.HoraInicio/60) < @HoraActual AND (hor.HoraFin/60) > @HoraActual AND hor.Dias LIKE '%'+@Dia+'%' /*Horarios*/
AND e.HoraTiempo IS NULL /* Expceciones*/
 AND i1.FechaBorrado IS NULL
)

DECLARE @infos BIGINT /* Contiene los informaticos disponibles para la asignacion*/
SET @infos = (
			SELECT TOP 1 InformaticoID FROM		
			(
				SELECT i.InformaticoID
				FROM 
							/*Filtro por roles y Circuito*/
								(SELECT i1.InformaticoID, c.CircuitoID	FROM Informatico as i1 
								INNER JOIN InformaticoCircuito as ic ON i1.InformaticoID = ic.InformaticoID
								INNER JOIN Circuito as c ON ic.CircuitoID = c.CircuitoID
								INNER JOIN RolInformatico as ri ON i1.InformaticoID=ri.InformaticoID
								INNER JOIN Rol as r ON ri.RolID=r.RolID		
								WHERE ic.CircuitoID = @Circuito AND r.NombreRol = 'Personal de Soporte' AND i1.FechaBorrado IS NULL) as i
							INNER JOIN 
							/*Filtro por Habilidad*/
								(SELECT InformaticoID, h.HabilidadID, NombreHabilidad FROM 
								InformaticoHabilidad as ih 
								INNER JOIN Habilidad as h ON ih.HabilidadID=h.HabilidadID
								WHERE ih.HabilidadID = @Habilidad AND h.FechaBorrado IS NULL) as h
							ON i.InformaticoID=h.InformaticoID
							INNER JOIN
							/*Filtro de Horarios y excepciones*/
								(SELECT InformaticoID FROM Informatico as i
								INNER JOIN Horario as h ON i.Horario=h.HorarioID
								LEFT JOIN 
								(SELECT Informatico, HoraTiempo FROM Excepcion
								WHERE Comienzo < GETDATE() AND HoraTiempo < GETDATE() AND FechaBorrado IS NULL)
								 as e ON e.Informatico=i.InformaticoID														
								WHERE (h.HoraInicio/60) < @HoraActual AND (h.HoraFin/60) > @HoraActual AND DIAS LIKE '%'+@Dia+'%' /*Horario*/
								AND e.HoraTiempo IS NULL /*Excepciones*/
								AND i.FechaBorrado IS NULL 	AND h.FechaBorrado IS NULL
								) as hor
							ON i.InformaticoID = hor.InformaticoID
						GROUP BY i.InformaticoID		
						) as Infos
			LEFT JOIN 		
					(
					/*Se selecciona el usuario, es @count-1 en caso de que se tenga que volver a iniciar el ciclo de seleccion*/
					SELECT TOP (@count-1) AsignacionID, Informatico FROM Asignacion WHERE Circuito = @Circuito AND Habilidad = @Habilidad ORDER BY AsignacionID DESC
					) as A 
				ON Infos.InformaticoID=a.Informatico
				WHERE AsignacionID IS NULL			
		)
		SELECT @infos		
END

GO
/****** Object:  StoredProcedure [dbo].[GetUltimasAsignaciones]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetUltimasAsignaciones]
	-- Add the parameters for the stored procedure here
	@Informatico INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

		SELECT TOP 10 AsignacionID, c.NombreCircuito, h.NombreHabilidad, i.NombreCompleto, a.Fecha
		FROM 
		Asignacion as a 
		INNER JOIN Circuito as c ON a.Circuito=c.CircuitoID
		INNER JOIN Habilidad as h ON a.Habilidad=h.HabilidadID
		INNER JOIN Informatico as i ON i.InformaticoID=a.Informatico
		WHERE RealizadaPor = @Informatico
		ORDER BY AsignacionID DESC
END

GO
/****** Object:  StoredProcedure [dbo].[IsHabilidadEnCircuito]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[IsHabilidadEnCircuito]
	-- Add the parameters for the stored procedure here
	@Circuito BIGINT,
	@Habilidad BIGINT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT COUNT(HabilidadID) 'Existe'
	FROM Habilidad
	WHERE HabilidadID = @Habilidad
	AND Circuito = @Circuito
	AND FechaBorrado IS NULL
END

GO
/****** Object:  StoredProcedure [dbo].[ListarCoordinadoresDeTI]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ListarCoordinadoresDeTI]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT c.CircuitoID, c.NombreCircuito 'Circuito', info.InformaticoID, info.NombreCompleto 'Informatico'
	FROM
	(SELECT c.CircuitoID, c.NombreCircuito
	FROM Circuito as c
	WHERE c.FechaBorrado IS NULL) as c
	LEFT JOIN
	(SELECT i2.InformaticoID, i2.NombreCompleto, ic.CircuitoID
	FROM Informatico as i2 
	INNER JOIN InformaticoCircuito as ic ON i2.InformaticoID=ic.InformaticoID
	WHERE 
	i2.EsCoordinador = 1
	AND i2.FechaBorrado IS NULL) as info
	ON c.CircuitoID=info.CircuitoID 
    
END

GO
/****** Object:  StoredProcedure [dbo].[ListarExcepciones]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ListarExcepciones] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT ExcepcionID, Informatico, Comienzo, HoraTiempo 
	FROM Excepcion WHERE FechaBorrado IS NULL AND HoraTiempo > GETDATE()
END

GO
/****** Object:  StoredProcedure [dbo].[ListarInformaticos]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ListarInformaticos]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT i.InformaticoID, i.NombreCompleto, r.NombreRol, c.CircuitoID, h.HorarioID
	FROM Informatico as i
	INNER JOIN Horario as h ON i.Horario=h.HorarioID
	INNER JOIN RolInformatico as ri ON ri.InformaticoID = i.InformaticoID
	INNER JOIN Rol as r ON ri.RolID=r.RolID 
	LEFT JOIN InformaticoCircuito as ic ON i.InformaticoID=ic.InformaticoID 
	LEFT JOIN Circuito as c ON ic.CircuitoID=c.CircuitoID  
	WHERE i.FechaBorrado IS NULL
END

GO
/****** Object:  StoredProcedure [dbo].[ListarInformaticosPorCircuito]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ListarInformaticosPorCircuito]
	@ID int 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT i.InformaticoID, i.NombreCompleto, r.NombreRol, c.CircuitoID
	FROM Informatico as i 
	INNER JOIN InformaticoCircuito as ic ON i.InformaticoID=ic.InformaticoID
	INNER JOIN Circuito as c ON ic.CircuitoID=c.CircuitoID
	INNER JOIN RolInformatico as ri ON ri.InformaticoID = i.InformaticoID
	INNER JOIN Rol as r ON r.RolID = ri.RolID
	WHERE 
	i.FechaBorrado IS NULL
	AND c.FechaBorrado IS NULL
	AND c.CircuitoID = @ID
END

GO
/****** Object:  StoredProcedure [dbo].[NuevoCoordinadorParaCircuito]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[NuevoCoordinadorParaCircuito]
	-- Add the parameters for the stored procedure here
	@Circuito BIGINT, 
	@CoordinadorNuevo BIGINT 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    BEGIN TRANSACTION T1;

--En caso de que existan varios coordinadores este script los elimina. 
DECLARE @VariosCoordinadores INT 
SET @VariosCoordinadores =
(
	SELECT COUNT(i.InformaticoID)
	FROM Informatico as i 
	INNER JOIN InformaticoCircuito as ic ON i.InformaticoID=ic.InformaticoID
	WHERE EsCoordinador = 1
	AND ic.CircuitoID = @Circuito
	AND i.FechaBorrado IS NULL
)

IF(@VariosCoordinadores > 1) 
	BEGIN
		UPDATE Informatico 
			SET EsCoordinador = 0
		FROM Informatico as i, InformaticoCircuito as ic
		WHERE
		i.InformaticoID = ic.InformaticoID
		AND ic.CircuitoID = @Circuito
		AND i.FechaBorrado IS NULL
	END
--Fin de varios Coordinadores


DECLARE @CoordinadorActual BIGINT
SET @CoordinadorActual =
(
	--Busca el coordinador Actual para un circuito
	SELECT i.InformaticoID
	FROM Informatico as i
	INNER JOIN InformaticoCircuito as ic ON i.InformaticoID=ic.InformaticoID
	WHERE
	EsCoordinador = 1
	AND ic.CircuitoID = @Circuito
	AND i.FechaBorrado IS NULL
)

DECLARE @EstaEnMismoCircuito INT
SET @EstaEnMismoCircuito = 
(
	--Revisa que el coordinador nuevo este en el mismo circuito al que se va a asignar
	SELECT COUNT(i.InformaticoID)
	FROM
	Informatico as i 
	INNER JOIN InformaticoCircuito as ic ON i.InformaticoID=ic.InformaticoID
	WHERE ic.CircuitoID = @Circuito
	AND i.InformaticoID = @CoordinadorNuevo
	AND i.FechaBorrado IS NULL
)

IF(@EstaEnMismoCircuito = 1)
BEGIN
	IF(@CoordinadorActual > 0)
		UPDATE Informatico SET EsCoordinador = 0 WHERE InformaticoID = @CoordinadorActual
	UPDATE Informatico SET EsCoordinador = 1 WHERE InformaticoID = @CoordinadorNuevo	
END

COMMIT TRANSACTION T1;

SELECT 1

END

GO
/****** Object:  UserDefinedFunction [dbo].[udf_DayOfWeek]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_DayOfWeek](@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)
SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
WHEN 1 THEN 'Domingo'
WHEN 2 THEN 'Lunes'
WHEN 3 THEN 'Martes'
WHEN 4 THEN 'Miercoles'
WHEN 5 THEN 'Jueves'
WHEN 6 THEN 'Viernes'
WHEN 7 THEN 'Sabado'
END
RETURN (@rtDayofWeek)
END

GO
/****** Object:  Table [dbo].[Asignacion]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Asignacion](
	[AsignacionID] [bigint] IDENTITY(1,1) NOT NULL,
	[Circuito] [bigint] NOT NULL,
	[Habilidad] [bigint] NOT NULL,
	[Informatico] [bigint] NOT NULL,
	[RealizadaPor] [bigint] NOT NULL,
	[Fecha] [datetime] NOT NULL,
 CONSTRAINT [PK_Asignacion] PRIMARY KEY CLUSTERED 
(
	[AsignacionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Circuito]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Circuito](
	[CircuitoID] [bigint] IDENTITY(1,1) NOT NULL,
	[NombreCircuito] [varchar](50) NOT NULL,
	[CodigoCircuito] [varchar](10) NOT NULL,
	[FechaBorrado] [datetime] NULL,
 CONSTRAINT [PK_Circuito] PRIMARY KEY CLUSTERED 
(
	[CircuitoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[CircuitoHabilidad]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CircuitoHabilidad](
	[CurcuitoHabilidadID] [bigint] IDENTITY(1,1) NOT NULL,
	[CircuitoID] [bigint] NOT NULL,
	[HabilidadID] [bigint] NOT NULL,
	[FechaBorrado] [datetime] NULL,
 CONSTRAINT [PK_CircuitoHabilidad] PRIMARY KEY CLUSTERED 
(
	[CurcuitoHabilidadID] ASC,
	[CircuitoID] ASC,
	[HabilidadID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Excepcion]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Excepcion](
	[ExcepcionID] [bigint] IDENTITY(1,1) NOT NULL,
	[Informatico] [bigint] NOT NULL,
	[Comienzo] [datetime] NOT NULL,
	[HoraTiempo] [datetime] NOT NULL,
	[FechaBorrado] [datetime] NULL,
 CONSTRAINT [PK_Excepcion] PRIMARY KEY CLUSTERED 
(
	[ExcepcionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Habilidad]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Habilidad](
	[HabilidadID] [bigint] IDENTITY(1,1) NOT NULL,
	[NombreHabilidad] [varchar](100) NOT NULL,
	[Circuito] [bigint] NOT NULL,
	[FechaBorrado] [datetime] NULL,
 CONSTRAINT [PK_Habilidad] PRIMARY KEY CLUSTERED 
(
	[HabilidadID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Horario]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Horario](
	[HorarioID] [bigint] IDENTITY(1,1) NOT NULL,
	[NombreHorario] [varchar](50) NOT NULL,
	[HoraInicio] [int] NOT NULL,
	[HoraFin] [int] NOT NULL,
	[Dias] [varchar](7) NOT NULL,
	[FechaBorrado] [datetime] NULL,
 CONSTRAINT [PK_Horario] PRIMARY KEY CLUSTERED 
(
	[HorarioID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Informatico]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Informatico](
	[InformaticoID] [bigint] IDENTITY(1,1) NOT NULL,
	[NombreCompleto] [varchar](40) NOT NULL,
	[EsCoordinador] [bit] NOT NULL,
	[Horario] [bigint] NOT NULL,
	[FechaBorrado] [datetime] NULL,
 CONSTRAINT [PK_Informatico_1] PRIMARY KEY CLUSTERED 
(
	[InformaticoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[InformaticoCircuito]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InformaticoCircuito](
	[CircuitoID] [bigint] NOT NULL,
	[InformaticoID] [bigint] NOT NULL,
	[FechaBorrado] [datetime] NULL,
 CONSTRAINT [PK_InformaticoCircuito] PRIMARY KEY CLUSTERED 
(
	[CircuitoID] ASC,
	[InformaticoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[InformaticoHabilidad]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InformaticoHabilidad](
	[InformaticoHabilidadID] [bigint] IDENTITY(1,1) NOT NULL,
	[InformaticoID] [bigint] NOT NULL,
	[HabilidadID] [bigint] NOT NULL,
	[FechaBorrado] [datetime] NULL,
 CONSTRAINT [PK_InformaticoHabilidad] PRIMARY KEY CLUSTERED 
(
	[InformaticoHabilidadID] ASC,
	[InformaticoID] ASC,
	[HabilidadID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Log]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Log](
	[LogId] [bigint] IDENTITY(1,1) NOT NULL,
	[InformaticoID] [bigint] NOT NULL,
	[LogText] [varchar](1000) NOT NULL,
	[LogDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED 
(
	[InformaticoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Rol]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Rol](
	[RolID] [bigint] IDENTITY(1,1) NOT NULL,
	[NombreRol] [varchar](50) NOT NULL,
	[FechaBorrado] [datetime] NULL,
 CONSTRAINT [PK_Rol] PRIMARY KEY CLUSTERED 
(
	[RolID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[RolInformatico]    Script Date: 6/7/2013 6:55:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RolInformatico](
	[InformaticoRolID] [bigint] IDENTITY(1,1) NOT NULL,
	[InformaticoID] [bigint] NOT NULL,
	[RolID] [bigint] NOT NULL,
	[FechaBorrado] [datetime] NULL,
 CONSTRAINT [PK_InformaticoRol] PRIMARY KEY CLUSTERED 
(
	[InformaticoRolID] ASC,
	[InformaticoID] ASC,
	[RolID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Habilidad] ADD  CONSTRAINT [DF_Habilidad_Circuito]  DEFAULT ((1)) FOR [Circuito]
GO
ALTER TABLE [dbo].[Informatico] ADD  CONSTRAINT [DF_Informatico_EsCoordinador]  DEFAULT ((0)) FOR [EsCoordinador]
GO
ALTER TABLE [dbo].[Informatico] ADD  CONSTRAINT [DF_Informatico_Horario]  DEFAULT ((0)) FOR [Horario]
GO
ALTER TABLE [dbo].[Asignacion]  WITH CHECK ADD  CONSTRAINT [FK_Asignacion_Circuito] FOREIGN KEY([Circuito])
REFERENCES [dbo].[Circuito] ([CircuitoID])
GO
ALTER TABLE [dbo].[Asignacion] CHECK CONSTRAINT [FK_Asignacion_Circuito]
GO
ALTER TABLE [dbo].[Asignacion]  WITH CHECK ADD  CONSTRAINT [FK_Asignacion_Habilidad] FOREIGN KEY([Habilidad])
REFERENCES [dbo].[Habilidad] ([HabilidadID])
GO
ALTER TABLE [dbo].[Asignacion] CHECK CONSTRAINT [FK_Asignacion_Habilidad]
GO
ALTER TABLE [dbo].[Asignacion]  WITH CHECK ADD  CONSTRAINT [FK_Asignacion_Informatico] FOREIGN KEY([Informatico])
REFERENCES [dbo].[Informatico] ([InformaticoID])
GO
ALTER TABLE [dbo].[Asignacion] CHECK CONSTRAINT [FK_Asignacion_Informatico]
GO
ALTER TABLE [dbo].[Asignacion]  WITH CHECK ADD  CONSTRAINT [FK_AsignacionRealizadoPor_Informatico] FOREIGN KEY([RealizadaPor])
REFERENCES [dbo].[Informatico] ([InformaticoID])
GO
ALTER TABLE [dbo].[Asignacion] CHECK CONSTRAINT [FK_AsignacionRealizadoPor_Informatico]
GO
ALTER TABLE [dbo].[CircuitoHabilidad]  WITH CHECK ADD  CONSTRAINT [FK_Circuito_Habilidad] FOREIGN KEY([CircuitoID])
REFERENCES [dbo].[Circuito] ([CircuitoID])
GO
ALTER TABLE [dbo].[CircuitoHabilidad] CHECK CONSTRAINT [FK_Circuito_Habilidad]
GO
ALTER TABLE [dbo].[CircuitoHabilidad]  WITH CHECK ADD  CONSTRAINT [FK_Habilidad_Circuito] FOREIGN KEY([HabilidadID])
REFERENCES [dbo].[Habilidad] ([HabilidadID])
GO
ALTER TABLE [dbo].[CircuitoHabilidad] CHECK CONSTRAINT [FK_Habilidad_Circuito]
GO
ALTER TABLE [dbo].[Excepcion]  WITH CHECK ADD  CONSTRAINT [FK_Excepcion_Informatico] FOREIGN KEY([Informatico])
REFERENCES [dbo].[Informatico] ([InformaticoID])
GO
ALTER TABLE [dbo].[Excepcion] CHECK CONSTRAINT [FK_Excepcion_Informatico]
GO
ALTER TABLE [dbo].[Habilidad]  WITH CHECK ADD  CONSTRAINT [FK_Habilidad_HabilidadCircuito] FOREIGN KEY([Circuito])
REFERENCES [dbo].[Circuito] ([CircuitoID])
GO
ALTER TABLE [dbo].[Habilidad] CHECK CONSTRAINT [FK_Habilidad_HabilidadCircuito]
GO
ALTER TABLE [dbo].[InformaticoCircuito]  WITH CHECK ADD  CONSTRAINT [FK_CircuitoInformaticoID] FOREIGN KEY([CircuitoID])
REFERENCES [dbo].[Circuito] ([CircuitoID])
GO
ALTER TABLE [dbo].[InformaticoCircuito] CHECK CONSTRAINT [FK_CircuitoInformaticoID]
GO
ALTER TABLE [dbo].[InformaticoCircuito]  WITH CHECK ADD  CONSTRAINT [FK_InformaticoCircuitoID] FOREIGN KEY([InformaticoID])
REFERENCES [dbo].[Informatico] ([InformaticoID])
GO
ALTER TABLE [dbo].[InformaticoCircuito] CHECK CONSTRAINT [FK_InformaticoCircuitoID]
GO
ALTER TABLE [dbo].[InformaticoHabilidad]  WITH CHECK ADD  CONSTRAINT [FK_HabilidadInformatico] FOREIGN KEY([HabilidadID])
REFERENCES [dbo].[Habilidad] ([HabilidadID])
GO
ALTER TABLE [dbo].[InformaticoHabilidad] CHECK CONSTRAINT [FK_HabilidadInformatico]
GO
ALTER TABLE [dbo].[InformaticoHabilidad]  WITH CHECK ADD  CONSTRAINT [FK_InformaticoHabilidad] FOREIGN KEY([InformaticoID])
REFERENCES [dbo].[Informatico] ([InformaticoID])
GO
ALTER TABLE [dbo].[InformaticoHabilidad] CHECK CONSTRAINT [FK_InformaticoHabilidad]
GO
ALTER TABLE [dbo].[Log]  WITH CHECK ADD  CONSTRAINT [FK_Log_Informatico] FOREIGN KEY([InformaticoID])
REFERENCES [dbo].[Informatico] ([InformaticoID])
GO
ALTER TABLE [dbo].[Log] CHECK CONSTRAINT [FK_Log_Informatico]
GO
ALTER TABLE [dbo].[RolInformatico]  WITH CHECK ADD  CONSTRAINT [FK_InformaticoRol] FOREIGN KEY([InformaticoID])
REFERENCES [dbo].[Informatico] ([InformaticoID])
GO
ALTER TABLE [dbo].[RolInformatico] CHECK CONSTRAINT [FK_InformaticoRol]
GO
ALTER TABLE [dbo].[RolInformatico]  WITH CHECK ADD  CONSTRAINT [FK_RolInformatico] FOREIGN KEY([RolID])
REFERENCES [dbo].[Rol] ([RolID])
GO
ALTER TABLE [dbo].[RolInformatico] CHECK CONSTRAINT [FK_RolInformatico]
GO
